package org.zjvis.datascience.web.controller;

import cn.hutool.db.Entity;
import cn.hutool.db.meta.MetaUtil;
import cn.hutool.db.meta.Table;
import cn.weiguangfu.swagger2.plus.annotation.ApiGroup;
import cn.weiguangfu.swagger2.plus.enums.ApiExecutionEnum;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import javax.validation.Valid;
import org.apache.commons.collections.CollectionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
import org.zjvis.datascience.common.annotation.CategoryAuth;
import org.zjvis.datascience.common.annotation.DatasetAuth;
import org.zjvis.datascience.common.annotation.ProjectAuth;
import org.zjvis.datascience.common.annotation.ProjectRoleAuth;
import org.zjvis.datascience.common.constant.DatabaseConstant;
import org.zjvis.datascience.common.dto.*;
import org.zjvis.datascience.common.dto.dataset.DatasetNameTypeDTO;
import org.zjvis.datascience.common.dto.dataset.DatasetUpdateConfigDTO;
import org.zjvis.datascience.common.dto.user.UserDTO;
import org.zjvis.datascience.common.enums.MaskingEnum;
import org.zjvis.datascience.common.enums.ProjectAuthEnum;
import org.zjvis.datascience.common.enums.ProjectRoleAuthEnum;
import org.zjvis.datascience.common.exception.BaseErrorCode;
import org.zjvis.datascience.common.exception.DataScienceException;
import org.zjvis.datascience.common.model.ApiResult;
import org.zjvis.datascience.common.model.ApiResultCode;
import org.zjvis.datascience.common.util.DozerUtil;
import org.zjvis.datascience.common.util.JwtUtil;
import org.zjvis.datascience.common.util.RegexpUtil;
import org.zjvis.datascience.common.util.SqlUtil;
import org.zjvis.datascience.common.util.db.JDBCUtil;
import org.zjvis.datascience.common.vo.SqlCategoryVO;
import org.zjvis.datascience.common.vo.TaskSaveVO;
import org.zjvis.datascience.common.vo.dataset.*;
import org.zjvis.datascience.common.vo.dataset.DatasetIdVO.Id;
import org.zjvis.datascience.common.vo.db.*;
import org.zjvis.datascience.common.vo.project.ProjectDataSourceVO;
import org.zjvis.datascience.common.vo.project.ProjectIdVO;
import org.zjvis.datascience.service.*;
import org.zjvis.datascience.service.dataset.DatasetService;
import org.zjvis.datascience.service.dataset.ImportDataService;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @description 数据集管理接口 Controller
 * @date 2021-12-23
 */
@Api(tags = "数据集管理")
@RestController
@RequestMapping("/dataset")
public class DatasetController {

    private final static Logger logger = LoggerFactory.getLogger(DatasetController.class);

    @Autowired
    private DatasetService datasetService;

    @Autowired
    private ImportDataService importDataService;

    @Autowired
    private DatasetProjectService datasetProjectService;

    @Autowired
    private DataLevelService dataLevelService;

    @Autowired
    private ExternalDataSourceService externalDataSourceService;

    @Autowired
    private SemanticService semanticService;

    @Autowired
    private SqlQueryService sqlQueryService;

    @Autowired
    private DatasetActionService datasetActionService;

    @PostMapping("/queryById")
    @ApiOperation(value = "根据数据集id获取数据集")
    public ApiResult<DatasetDTO> queryById(
            @DatasetAuth()
            @Validated(value = DatasetIdVO.Id.class) @RequestBody DatasetIdVO datasetVO) {
        DatasetDTO res = datasetService.queryById(datasetVO.getId());
        return ApiResult.valueOf(DozerUtil.mapper(res, DatasetDTO.class));
    }

    @PostMapping("/queryByCategoryId")
    @ApiOperation(value = "根据分类目录id获取数据集")
    public ApiResult<List<DatasetDTO>> queryByCategoryId(
            @CategoryAuth(field = "categoryId")
            @Validated(value = CategoryIdOfTheDatasetVO.CategoryId.class) @RequestBody CategoryIdOfTheDatasetVO datasetVO) {
        List<DatasetDTO> res = datasetService.queryByCategoryId(datasetVO.getCategoryId());
        return ApiResult.valueOf(DozerUtil.mapper(res, DatasetDTO.class));
    }

    @PostMapping("/queryByProjectId")
    @ApiOperation(value = "根据项目id获取数据集")
    public ApiResult<List<DatasetNameTypeDTO>> queryByProjectId(
            @ProjectAuth(auth = ProjectAuthEnum.READ) @Validated(value = ProjectIdVO.ProjectId.class) @RequestBody ProjectIdVO projectIdVO) {
        List<DatasetNameTypeDTO> res = datasetService.queryByProjectId(projectIdVO.getProjectId());
        return ApiResult.valueOf(DozerUtil.mapper(res, DatasetNameTypeDTO.class));
    }

//    /**
//     * 需要移除， 这里对应的应该是 /projects/datasets/queryProjectDataset2
//     * datascience-web/src/main/java/org/zjvis/datascience/web/controller/DatasetProjectController.java -》 line 112
//     *
//     * @param projectIdVO
//     * @return
//     */
//    @PostMapping("/queryByProjectId2")
//    @ApiOperation(value = "根据项目id获取数据集 （包括pipeline里面的结果表信息）")
//    @Deprecated
//    public ApiResult<List<ProjectDataSourceVO>> queryByProjectId2(
//            @ProjectAuth(auth = ProjectAuthEnum.READ) @Validated(value = ProjectIdVO.ProjectId.class) @RequestBody ProjectIdVO projectIdVO) {
//        List<ProjectDataSourceVO> sourceVOS = datasetProjectService.queryProjectDataset2(projectIdVO.getProjectId(), null, null);
//        return ApiResult.valueOf(sourceVOS);
//    }


    @PostMapping(value = "/update")
    @ResponseBody
    @ApiOperation(value = "修改数据集")
    public ApiResult<Boolean> update(
            @DatasetAuth()
            @Validated(value = UpdateDatasetVO.Id.class) @RequestBody UpdateDatasetVO datasetVO) {
        UserDTO user = JwtUtil.getCurrentUserDTO();
        String name = datasetVO.getName();
        if (RegexpUtil.checkGPSpecialCharacter(name)) {
            return ApiResult.valueOf(ApiResultCode.CONTAIN_SPECIAL_CHARACTER);
        }
        DatasetDTO datasetDTO = DozerUtil.mapper(datasetVO, DatasetDTO.class);
        datasetDTO.setUserId(user.getId());
        datasetDTO.setGmtModifier(user.getId());
        return ApiResult.valueOf(datasetService.update(datasetDTO));
    }

    @PostMapping(value = "/delete")
    @ResponseBody
    @ApiOperation(value = "删除数据集")
    public ApiResult<Boolean> delete(
            @DatasetAuth() @Validated(value = DatasetIdVO.Id.class) @RequestBody DatasetIdVO datasetVO) {
        //TODO 物理删除，只有当前用户为userId才能删除
        UserDTO user = JwtUtil.getCurrentUserDTO();
        if (user.getId().equals(0L)) {
            return ApiResult.valueOf(ApiResultCode.NO_AUTH);
        }

        if (datasetVO.getTransferUserId() == null || datasetVO.getTransferUserId() < 1) {
            List<DatasetProjectDTO> datasetProjectList = datasetProjectService
                .queryByDatasetId(datasetVO.getId());
            if (CollectionUtils.isNotEmpty(datasetProjectList)) {
                return ApiResult
                    .valueOf(ApiResultCode.SYS_ERROR, Boolean.FALSE,
                        "该数据集已被加载到项目中，请先从项目中移出再删除，如果是其他人在用请转移给某个用户!");
            }
        }

        DatasetDTO datasetDTO = new DatasetDTO();
        datasetDTO.setId(datasetVO.getId());
        datasetDTO.setUserId(user.getId());

        boolean ret = datasetService.deleteOrTransferUser(datasetDTO, datasetVO.getTransferUserId());

        return ApiResult.valueOf(ret);
    }

    @PostMapping(value = "/queryDataById")
    @ResponseBody
    @ApiOperation(value = "获取指定id的数据集数据")
    public ApiResult<Object> queryDataById(HttpServletRequest request,
                                           @DatasetAuth(checkSystemAdmin = true) @Validated(value = Id.class) @RequestBody DatasetIdVO datasetVO) {
        String scheme = request.getScheme();
        UserDTO user = JwtUtil.getCurrentUserDTO();
        JSONObject jo = datasetService.queryDataById(datasetVO.getId(), null, null);
        JSONObject data = jo.getObject("data", JSONObject.class);
        //http数据导入的url需要添加当前域名
        if ("http".equals(data.getString("type"))) {
            String dataConfig = String.format("%s://%s%s",
                    scheme,
                    request.getHeader("Host"),
                    data.getString("dataConfig")
            );
            data.put("dataConfig", dataConfig);
            jo.put("data", data);
        }
        if (jo.getInteger("code") != 200) {
            return new ApiResult(jo.getInteger("code"), jo.getString("errMsg"));
        } else {
            datasetActionService.updateUnread(user.getId(), datasetVO.getId());
            return ApiResult.valueOf(jo.get("data"));
        }
    }


    @PostMapping(value = "/userTables")
    @ResponseBody
    @ApiOperation(value = "获取用户的所有表信息")
    public ApiResult<Object> queryAllTables() {
        UserDTO user = JwtUtil.getCurrentUserDTO();
        return ApiResult.valueOf(sqlQueryService.getTablesByUserId(user.getId()));
    }

    @PostMapping(value = "/userTableCheck")
    @ResponseBody
    @ApiOperation(value = "获取用户的所有表信息")
    public ApiResult<Object> userTableCheck(HttpServletRequest request, @RequestBody String param) {
        UserDTO user = new UserDTO();
        String[] params = param.split("--");
        Long userId = Long.parseLong(params[0]);
        String tableName = params[1];
        user.setId(userId);
        List<DatasetDTO> tables = sqlQueryService.getTablesByUserId(user.getId());
        for (DatasetDTO dataset : tables) {
            if (tableName.equals(dataset.getName())) {
                return ApiResult.valueOf(ApiResultCode.SUCCESS);
            }
        }
        return ApiResult.valueOf(ApiResultCode.DATA_NULL);
    }

    /**
     * NAP-2711  后端先自己跑通，然后前端在切换
     * 已延期， 还没有添加category
     * @param json
     * @return
     */
    @PostMapping(value = "/queryDataBySql2")
    @ResponseBody
    @ApiOperation(value = "Sql查询的数据集数据")
    public ApiResult<DataDto> queryDataBySql2(@RequestBody(required = false) JSONObject json) {
        if (null == json || json.isEmpty()) {
            logger.warn("API /task/queryDataBySql2 failed, since {}", "request param is empty");
            return ApiResult.valueOf(ApiResultCode.PARAM_ERROR, null, "request param is empty");
        }
        String sql = json.getString("sql");
        String category = json.getString("category");
        SqlQueryDTO sqlQueryDTO = sqlQueryService.queryDataBySql(sql, category, DatabaseConstant.GP_SQL_PREVIEW_COUNT);
        if (sqlQueryDTO.getCode() != 200) {
            return new ApiResult(sqlQueryDTO.getCode(), sqlQueryDTO.getErrMsg());
        } else {
            return ApiResult.valueOf(sqlQueryDTO.getData());
        }
    }

    @PostMapping(value = "/queryDataBySql")
    @ResponseBody
    @ApiOperation(value = "Sql查询的数据集数据")
    public ApiResult<DataDto> queryDataBySql(@RequestBody(required = false) String sql) {
        if (null == sql || sql.isEmpty()) {
            logger.warn("API /task/queryDataBySql failed, since {}", "request param is empty");
            return ApiResult.valueOf(ApiResultCode.PARAM_ERROR, null, "request param is empty");
        }
        SqlQueryDTO sqlQueryDTO = sqlQueryService
                .queryDataBySql(sql, DatabaseConstant.GP_SQL_PREVIEW_COUNT);
        if (sqlQueryDTO.getCode() != 200) {
            return new ApiResult(sqlQueryDTO.getCode(), sqlQueryDTO.getErrMsg());
        } else {
            return ApiResult.valueOf(sqlQueryDTO.getData());
        }
    }

    @RequestMapping("/exportToCsv")
    @ApiOperation(value = "导出SQL查询结果为CSV")
    public void exportToCsv(HttpServletResponse response, @RequestBody(required = false) String sql) {
        sqlQueryService.exportToCsv(response, sql);
    }

    @RequestMapping("/exportToXlsx")
    @ApiOperation(value = "导出SQL查询结果为Xls", produces = "application/octet-stream")
    public void exportToXlsx(HttpServletResponse response, @RequestBody(required = false) String sql) {
        sqlQueryService.exportToXlsx(response, sql);
    }


    @PostMapping(value = "/sqlCategory")
    @ResponseBody
    @ApiOperation(value = "查看执行Sql查询的日志")
    public ApiResult<List<SqlCategoryDTO>> sqlCategory() {
        UserDTO user = JwtUtil.getCurrentUserDTO();
        List<SqlCategoryDTO> res = sqlQueryService.getSqlCategory(user.getId());
        return ApiResult.valueOf(DozerUtil.mapper(res, SqlCategoryDTO.class));
    }

    @PostMapping(value = "/deleteAllSqlCategory")
    @ResponseBody
    @ApiOperation(value = "删除所有Sql查询的日志")
    public ApiResult<Integer> deleteAllSqlCategory() {
        UserDTO user = JwtUtil.getCurrentUserDTO();
        int res = sqlQueryService.deleteAllSqlCategory(user.getId());
        return ApiResult.valueOf(res);
    }

    @PostMapping(value = "/deleteSqlCategory")
    @ResponseBody
    @ApiOperation(value = "删除sql查询日志")
    public ApiResult<List<Long>> deleteSqlCategory(
            @RequestBody List<SqlCategoryVO> sqlCategoryVOS) {
        Long userId = JwtUtil.getCurrentUserId();
        List<Long> res = sqlQueryService.deleteSqlCategory(sqlCategoryVOS, userId);
        return ApiResult.valueOf(res);
    }

    @PostMapping(value = "/listMaskingType")
    @ResponseBody
    @ApiOperation(value = "获取脱敏类型")
    public ApiResult<Object> listMaskingType() {
        MaskingEnum[] me = MaskingEnum.values();
        JSONArray ja = new JSONArray();
        for (MaskingEnum m : me) {
            JSONObject jo = new JSONObject();
            jo.put("maskingTypeName", m.getDesc());
            jo.put("maskingType", m.getValue());
            ja.add(jo);
        }
        return ApiResult.valueOf(ja);
    }

    @PostMapping(value = "/listDataLevel")
    @ResponseBody
    @ApiOperation(value = "获取数据分级列表")
    public ApiResult<Object> listDataLevel() {
        return ApiResult.valueOf(dataLevelService.list());
    }

    @PostMapping(value = "/database/connectionTest")
    @ResponseBody
    @ApiOperation(value = "数据库连接测试并获取库名")
    public ApiResult<Object> databaseConnectionTest(@RequestBody DbConnectionTestVO conTest) {
        JSONObject jo = datasetService.databaseConnectionTest(conTest);
        if (jo.getInteger("code") == 500) {
            return new ApiResult(500, jo.getString("errMsg"));
        } else {
            return ApiResult.valueOf(jo.get("data"));
        }
    }

    @PostMapping(value = "/database/getTables")
    @ResponseBody
    @ApiOperation(value = "获取指定数据库下所以有表名")
    public ApiResult<Object> getTables(@RequestBody DBWithBDNameVO dBWithBDNameVO) {
        JSONObject jo = datasetService.getTables(dBWithBDNameVO);
        if (jo.getInteger("code") == 500) {
            return new ApiResult(500, jo.getString("errMsg"));
        } else {
            return ApiResult.valueOf(jo.get("data"));
        }
    }

    @PostMapping(value = "/database/queryTableData")
    @ResponseBody
    @ApiOperation(value = "获取指定表数据")
    public ApiResult<PreviewDatasetVO> queryTableData(
            @Validated @RequestBody DBPreviewVO dBPreviewVO) {
        return ApiResult.valueOf(datasetService.queryTableData(dBPreviewVO));
    }


    @PostMapping(value = "/database/getTablesHead")
    @ResponseBody
    @ApiOperation(value = "获取指定数据库下所有表名")
    public ApiResult<Object> getTablesHead(
            @Validated @RequestBody DBGetTableHeadVO dBGetTableHeadVO) {
//    JSONObject jo = datasetService.getTablesHead(dBGetTableHeadVO);
        JSONObject jo = new JSONObject();
        Connection con = null;
        String server = dBGetTableHeadVO.getServer();
        Integer port = dBGetTableHeadVO.getPort();
        String database = dBGetTableHeadVO.getDatabaseName();
        String url = "";
        String sql = "";
        String dbType = dBGetTableHeadVO.getDatabaseType();
        dbType = dbType == null ? "" : dbType.trim().toLowerCase();

        switch (dbType) {
            case "mysql":
            case "rds-mysql":
                url = String.format(DatabaseConstant.MYSQL_JDBC_URL, server, port, database);
                sql = DatabaseConstant.MYSQL_SHOW_TABLES;
                break;
            case "oracle":
                sql = DatabaseConstant.ORACLE_SHOW_TABLES;
                String connectType = dBGetTableHeadVO.getConnectType();
                String connectValue = dBGetTableHeadVO.getConnectValue();
                switch (connectType == null ? "" : connectType.toLowerCase()) {
                    case "servicename":
                        url = String
                                .format(DatabaseConstant.ORACLE_JDBC_SERVICE_NAME_URL, server, port,
                                        connectValue);
                        break;
                    default:
                        url = String.format(DatabaseConstant.ORACLE_JDBC_SID_URL, server, port,
                                connectValue);
                }
                break;
            default:
                throw new DataScienceException(BaseErrorCode.DATASET_PREVIEW_UNSUPPORTED);
        }

        List<Entity> data;
        try {
            con = JDBCUtil
                    .getConnection(url, dBGetTableHeadVO.getUser(), dBGetTableHeadVO.getPassword());
            List<String> names = dBGetTableHeadVO.getTableNames();
            List<DbTableHeadDTO> tbheads = new ArrayList<>();
            DataSource ds = JDBCUtil
                    .getDataSource(url, dBGetTableHeadVO.getUser(), dBGetTableHeadVO.getPassword());
            for (String name : names) {
                Table tableMeta = MetaUtil.getTableMeta(ds, name);
                data = JDBCUtil.page(ds, tableMeta, 0, 10, dbType, true);
                String finalDbType = dbType;
                List<HeadVO> head = tableMeta.getColumns().stream()
                        .map(column ->
                                HeadVO.builder()
                                        //head和body字段名统一转小写
                                        .name(column.getName().toLowerCase())
                                        .type(SqlUtil.changeType(finalDbType, column))
                                        .build())
                        .collect(Collectors.toList());
                semanticService.recommendSemantic(head, data);
                DbTableHeadDTO dto = new DbTableHeadDTO();
                dto.setHead(head);
                dto.setTableName(name);
                tbheads.add(dto);
            }

            jo.put("data", tbheads);
            jo.put("code", 200);
        } catch (SQLException e) {
            throw new DataScienceException(BaseErrorCode.DATASET_QUERY_ERROR);
        } finally {
            JDBCUtil.close(con, null, null);
        }

        if (jo.getInteger("code") == 500) {
            return new ApiResult(500, jo.getString("errMsg"));
        } else {
            return ApiResult.valueOf(jo.get("data"));
        }
    }

    @PostMapping(value = "/database/importData")
    @ResponseBody
    @ApiOperation(value = "导入数据")
    public ApiResult<Boolean> importData(@Validated @RequestBody DBImportVO dBImportVO) {
        importDataService.importDataset(dBImportVO);
        return ApiResult.valueOf(true);
    }

    @PostMapping(value = "/database/importProgress")
    @ResponseBody
    @ApiOperation(value = "导入数据任务进度查询")
    public ApiResult<BatchImportDatasetProgressVO> importProgress() {
        return ApiResult.valueOf(importDataService.getProgress());
    }

    @PostMapping(value = "/database/importCancel")
    @ResponseBody
    public ApiResult<Boolean> cancel() {
        importDataService.cancel();
        return ApiResult.valueOf(true);
    }

    @GetMapping(value = "/database/importTasks")
    @ResponseBody
    public ApiResult<Map<String, PendingDatasetDTO>> getTasks() {
        return ApiResult.valueOf(importDataService.getTasks());
    }

    @PostMapping(value = "/database/listExternalDatabaseType")
    @ResponseBody
    @ApiOperation(value = "获取外部数据库类型")
    public ApiResult<Object> listExternalDatabaseType() {
        return ApiResult.valueOf("");
    }

    @PostMapping(value = "/database/updateConfig")
    @ResponseBody
    @ApiOperation(value = "更新数据库配置和定时配置")
    public ApiResult<Object> updateConfig(
            @DatasetAuth()
            @Validated @RequestBody DatasetUpdateConfigDTO datasetUpdateConfigDTO) {
        datasetService.updateConfig(datasetUpdateConfigDTO);
        return ApiResult.valueOf(true);
    }

//  @PostMapping(value = "/database/checkSchedule")
//  @ResponseBody
//  @ApiOperation(value = "检测是否可以定时配置")
//  public ApiResult<List<Boolean>> checkSchedule(
//          @DatasetAuth()
//          @Validated @RequestBody DatasetUpdateConfigDTO datasetUpdateConfigDTO) {
//    List<Boolean> resultList = new ArrayList<>();
//    if (datasetUpdateConfigDTO.getDatabaseType().equals("oracle")) {
//      for (String ignored : datasetUpdateConfigDTO.getTableNames()) {
//        resultList.add(true);
//      }
//    } else {
//      String dbType = datasetService.getDatabaseType(datasetUpdateConfigDTO.getDatabaseType());
//      String url = JDBCUtil.getUrl(datasetUpdateConfigDTO.getServer(), datasetUpdateConfigDTO.getPort(), datasetUpdateConfigDTO.getDatabaseName(), dbType,
//              datasetUpdateConfigDTO.getConnectType(), datasetUpdateConfigDTO.getConnectValue());
//      DataSource fromDs = JDBCUtil.getDataSource(url, datasetUpdateConfigDTO.getUser(), datasetUpdateConfigDTO.getPassword());
//      for (String tableName : datasetUpdateConfigDTO.getTableNames()) {
//        resultList.add(datasetService.checkSchedule(fromDs, tableName));
//      }
//    }
//    return ApiResult.valueOf(resultList);
//  }

    @ApiGroup(groups = TaskSaveVO.DatasetNameCheck.class, requestExecution = ApiExecutionEnum.INCLUDE)
    @PostMapping(value = "/checkDatasetName")
    public ApiResult<Boolean> checkDatasetName(@Validated(value = TaskSaveVO.DatasetNameCheck.class)
                                               @RequestBody TaskSaveVO vo) {
        JSONObject res = datasetService.checkDatasetName(vo.getCategoryId(), vo.getDatasetName());
        return ApiResult
                .valueOf(ApiResultCode.SUCCESS, res.getBoolean("exists"), res.getString("tips"));
    }

    @PostMapping(value = "/listExternalDataSource")
    @ResponseBody
    @ApiOperation(value = "获取外部数源信息")
    public ApiResult<List<ExternalDataSourceDTO>> listExternalDataSource(
            @RequestBody ExternalDataSourceDTO dto) {
        String type = dto.getType();
        return ApiResult.valueOf(externalDataSourceService.listExternalDataSource(type));
    }

    @PostMapping(value = "/queryDatasetUsedInfo")
    @ApiOperation(value = "获取当前用户在项目中数据集的使用情况")
    public ApiResult<Object> queryDatasetUsedInfo( @Valid @RequestBody QueryDatasetUsedInProjectVO vo) {
        vo.setUserId(JwtUtil.getCurrentUserId());
        Object obj = datasetProjectService.queryDatasetUsedInfo(vo);
        return ApiResult.valueOf(obj);
    }
}
